import pymysql

# pymysqlpool.logger.setLevel('DEBUG')

# 创建数据库SQL
# CREATE DATABASE IF NOT EXISTS student_quality_score_system DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# 数据库配置
config = {'host': '127.0.0.1',                          # 数据库主机IP
          'user': 'root',                               # 用户名
          'password': 'root',                           # 密码
          'database': 'student_quality_score_system',   # 数据库名称
          'autocommit': True,                           # 自动提交
          'cursorclass': pymysql.cursors.DictCursor     # 查询结果使用字典类型(默认为元组类型)
          }


# 获取数据库连接对象
def get_db_connection():
    # 通过数据库连接池获取数据库连接对象
    # connection = pymysqlpool.ConnectionPool(size=2, maxsize=3, pre_create_num=2, name='db_pool', **config)
    # 获取数据库连接对象
    connection = pymysql.connect(**config)
    return connection

class Student:
    def __init__(self, stu_name, stu_class, stu_id=None):
        self.stu_id = stu_id
        self.stu_name = stu_name
        self.stu_class = stu_class

    # 自定义打印学生对象时的字符串格式
    def __str__(self):
        return f'学号:{self.stu_id} 姓名:{self.stu_name}'

    # 获取所有学生信息(学生信息字典的列表) 静态方法
    @staticmethod
    def all():
        print("获取所有学生信息")
        with get_db_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute("select * from STUDENT")
                result = cursor.fetchall()
                return result

    # 通过ID获取学生信息(学生对象) 静态方法
    @staticmethod
    def get(stu_id):
        print("通过学号获取某一个学生信息")
        with get_db_connection() as connection:
            with connection.cursor() as cursor:
                sql = "select * from STUDENT where STU_ID = %s" % stu_id
                cursor.execute(sql)
                # 获取单条数据的字典对象
                stu_dict = cursor.fetchone()
                stu = Student(stu_id=stu_dict['STU_ID'],
                              stu_name=stu_dict['STU_NAME'],
                              stu_class=stu_dict['STU_CLASS'],
                              )
                return stu

    # 保存学生信息至数据库并返回学生ID
    def save(self):
        with get_db_connection() as connection:
            with connection.cursor() as cursor:
                sql = "insert into STUDENT(STU_NAME,STU_CLASS) values('%s', '%s') " \
                      % (self.stu_name, self.stu_class)
                cursor.execute(sql)
                # 返回新增数据的主键(ID)
                return cursor.lastrowid

        print(f"保存学生[{self.stu_name}]信息成功")

    # 更新学生信息至数据库 无返回值
    def update(self):
        with get_db_connection() as connection:
            with connection.cursor() as cursor:
                sql = "update STUDENT set STU_NAME='%s', STU_CLASS='%s' where STU_ID=%s " \
                      % (self.stu_name, self.stu_class, self.stu_id)
                cursor.execute(sql)
        print(f"修改学生[{self.stu_name}]信息成功")

    # 从数据库中删除学生信息 无返回值
    def delete(self):
        with get_db_connection() as connection:
            with connection.cursor() as cursor:
                sql = "delete from STUDENT where STU_ID=%s " \
                      % self.stu_id
                cursor.execute(sql)
        print(f"删除学生[{self.stu_name}]信息成功")


# 初始化数据库表结构
def init_db_table():
    with get_db_connection() as connection:
        with connection.cursor() as cursor:
            # 使用 execute() 方法执行 SQL，如果表存在则删除
            cursor.execute("DROP TABLE IF EXISTS STUDENT")
            # 使用预处理语句创建表
            sql = """CREATE TABLE STUDENT (
                          STU_ID     INT      PRIMARY KEY AUTO_INCREMENT,
                          STU_NAME   CHAR(20) NOT NULL,
                          STU_CLASS  CHAR(20) NOT NULL)"""
            cursor.execute(sql)


# 通过文件初始化数据库中的学生数据
def load_data_to_db():
    """从文件导入学生信息"""
    fr = open("student_list.txt", 'r', encoding="utf-8")
    fr.seek(0, 0)  # 重置读取位置
    lines = fr.readlines()
    lines.pop(0)  # 移除表头
    for line in lines:
        t = line.strip("\n").split('\t')  # 取出每行数据，即列表lines中的一个元素

        # 保存一个个学生信息至数据库
        with get_db_connection() as connection:
            with connection.cursor() as cursor:
                sql = "insert into STUDENT(STU_ID, STU_NAME, STU_CLASS) values('%s', '%s', '%s') " \
                      % (t[0], t[1], t[2])
                cursor.execute(sql)

    print('数据库初始化学生数据成功')


# 显示所有学生信息
def show_all_student():
    print("=" * 100)
    print("显示学生信息列表")
    student_all = Student.all()
    for row in student_all:
        print(row)
    print("=" * 100)


if __name__ == '__main__':
    # 初始化数据库表结构
    init_db_table()

    # 初始化数据库表结构
    load_data_to_db()

    # 显示所有学生信息
    show_all_student()

    # 创建学生对象并保存至数据库, 返回各自的ID(学号)
    new_stu1_id = Student(stu_name='张*三', stu_class='22级大数据技4班').save()
    new_stu2_id = Student(stu_name='李*四', stu_class='22级大数据技4班').save()

    # 显示所有学生信息
    show_all_student()

    # 通过学号获取某个学生对象
    stu1 = Student.get(new_stu1_id)
    # 打印学生对象
    print(stu1)

    # 修改学生信息
    stu1.stu_name = '张**'
    stu1.update()

    # 显示所有学生信息
    show_all_student()

    # 通过学号获取某个学生对象
    stu2 = Student.get(new_stu2_id)
    # 删除学生信息
    stu2.delete()

    # 显示所有学生信息
    show_all_student()
